package com.jlwl.houseservice.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jlwl.houseservice.entity.Review;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 评价Mapper接口
 */
@Mapper
public interface ReviewMapper extends BaseMapper<Review> {

    /**
     * 获取服务评分统计
     * @param serviceId 服务ID
     * @return 评分统计
     */
    @Select("SELECT " +
            "COUNT(*) as total, " +
            "AVG(rating) as avgRating, " +
            "SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) as rating5, " +
            "SUM(CASE WHEN rating = 4 THEN 1 ELSE 0 END) as rating4, " +
            "SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) as rating3, " +
            "SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) as rating2, " +
            "SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) as rating1 " +
            "FROM reviews " +
            "WHERE service_id = #{serviceId} AND status = 1")
    Map<String, Object> getServiceRatingStats(@Param("serviceId") Integer serviceId);

    /**
     * 获取员工评分统计
     * @param employeeId 员工ID
     * @return 评分统计
     */
    @Select("SELECT " +
            "COUNT(*) as total, " +
            "AVG(rating) as avgRating, " +
            "SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) as rating5, " +
            "SUM(CASE WHEN rating = 4 THEN 1 ELSE 0 END) as rating4, " +
            "SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) as rating3, " +
            "SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) as rating2, " +
            "SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) as rating1 " +
            "FROM reviews " +
            "WHERE employee_id = #{employeeId} AND status = 1")
    Map<String, Object> getEmployeeRatingStats(@Param("employeeId") Integer employeeId);

    /**
     * 获取评价标签统计
     * @param serviceId 服务ID
     * @return 标签统计
     */
    @Select("SELECT " +
            "SUBSTRING_INDEX(SUBSTRING_INDEX(t.tags, ',', n.n), ',', -1) as tag, " +
            "COUNT(*) as count " +
            "FROM reviews t " +
            "CROSS JOIN (SELECT 1 as n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) n " +
            "WHERE t.service_id = #{serviceId} AND t.status = 1 " +
            "AND LENGTH(t.tags) - LENGTH(REPLACE(t.tags, ',', '')) >= n.n - 1 " +
            "GROUP BY tag " +
            "ORDER BY count DESC")
    List<Map<String, Object>> getServiceTagStats(@Param("serviceId") Integer serviceId);
} 